package com.ihr360.insurance.service.impl;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.ihr360.commons.data.util.Pair;
import com.ihr360.commons.lang.CollectionUtils;
import com.ihr360.commons.lang.DateUtils;
import com.ihr360.commons.lang.StringUtils;
import com.ihr360.commons.model.type.BenefitCategory;
import com.ihr360.commons.model.type.BenefitsPayer;
import com.ihr360.commons.model.type.LedgerItemDataSource;
import com.ihr360.commons.vo.PageData;
import com.ihr360.excel.ExcelUtil;
import com.ihr360.excel.cellstyle.ExcelCellStyle;
import com.ihr360.excel.cellstyle.ExcelCellStyleFactory;
import com.ihr360.excel.constants.ExcelDefaultConfig;
import com.ihr360.excel.entity.CellComment;
import com.ihr360.excel.entity.ExportParams;
import com.ihr360.excel.utils.SpreadSheetConvertResultUtil;
import com.ihr360.excel.vo.CustomSpreadSheetValidateResult;
import com.ihr360.excel.vo.ImportRequest;
import com.ihr360.excel.vo.SpreadSheetCell;
import com.ihr360.excel.vo.SpreadSheetRow;
import com.ihr360.excel.vo.SpreadSheetValidatorError;
import com.ihr360.excel.vo.SpreadSheetValidatorRequest;
import com.ihr360.insurance.dao.MonthlyLedgerReposity;
import com.ihr360.insurance.entity.request.MonthlyLedgerRequest;
import com.ihr360.insurance.entity.response.LedgerResponse;
import com.ihr360.insurance.entity.snapshot.BenefitSnapshot;
import com.ihr360.insurance.entity.snapshot.CompanyBenefitSnapshot;
import com.ihr360.insurance.entity.vo.BenefitVo;
import com.ihr360.insurance.entity.vo.StaffBenefitVo;
import com.ihr360.insurance.model.dto.benefits.IBenefitPaymentConfig;
import com.ihr360.insurance.model.dto.ledger.MonthlyLedger;
import com.ihr360.insurance.model.dto.ledger.MonthlyLedgerSummary;
import com.ihr360.insurance.model.dto.staff.StaffBenefit;
import com.ihr360.insurance.model.meta.DynamicFooter;
import com.ihr360.insurance.model.meta.DynamicHeader;
import com.ihr360.insurance.model.meta.DynamicHeaderCell;
import com.ihr360.insurance.model.meta.DynamicRow;
import com.ihr360.insurance.model.meta.DynamicRowCell;
import com.ihr360.insurance.model.vo.BenefitGroupVo;
import com.ihr360.insurance.model.vo.BenefitTemplateVo;
import com.ihr360.insurance.model.vo.BenefitTypeVo;
import com.ihr360.insurance.model.vo.CompanyBenefitConfigVo;
import com.ihr360.insurance.model.vo.CompanyBenefitVo;
import com.ihr360.insurance.model.vo.MonthlyLedgerVo;
import com.ihr360.insurance.model.vo.PaymentConfigVo;
import com.ihr360.insurance.service.CompanyBenefitService;
import com.ihr360.insurance.service.MandatoryBenefitsTypeService;
import com.ihr360.insurance.service.MonthlyLedgerService;
import com.ihr360.insurance.service.MonthlyLedgerSummaryService;
import com.ihr360.insurance.service.StaffBenefitService;
import com.ihr360.rest.reposity.SimpleRestReposity;
import com.ihr360.rest.service.AbstractSimpleRestServiceImpl;
import com.ihr360.shared.basicstaffinfo.convertor.SyncedBasicStaffInfoConvertor;
import com.ihr360.shared.basicstaffinfo.dto.SyncedBasicStaffInfo;
import com.ihr360.shared.basicstaffinfo.service.BasicStaffInfoService;
import com.ihr360.utils.SIHFConstants;
import com.ihr360.utils.SIHFUtil;
import com.ihr360.validation.ValidationUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.criteria.Join;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.servlet.ServletOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

@Service
public class MonthlyLedgerServiceImpl extends AbstractSimpleRestServiceImpl<MonthlyLedger, Long> implements MonthlyLedgerService {

    private static final Logger logger = LoggerFactory.getLogger(MonthlyLedgerServiceImpl.class);

    private static final String REPLACE_STRING = "";
    private static final String TARGET_STRING = "保险";
    private static final BigDecimal DEVIDE_NUBMER = new BigDecimal(1000000);
    private static final String DEFAULT_CELL_VALUE_NUMBER = "0";
    private static final String EXCEL_EMPTY_VALUE = null;

    private static final String LEDGER_NOT_EXISTS_ERROR = "台账不存在，请刷新后重试";

    private static final String SI_INVALID = "【%s】社保方案无效";
    private static final String SI_DATE_INVALID = "【%s】社保起始、最后缴纳月不在台账月度内";
    private static final String HF_INVALID = "【%s】公积金方案无效";
    private static final String HF_DATE_INVALID = "【%s】公积金起始、最后缴纳月不在台账月度内";
    private static final String SI_NOT_CALCULATE_INFO = "【%s】社保数据为不计算";
    private static final String HF_NOT_CALCULATE_INFO = "【%s】公积金数据为不计算";
    private static final String NUMBER_INVALID = "【%s】不是合理数字";
    private static final String SI_BASE_NOT_SETTING = "【%s】未设置社保申报基数";
    private static final String HF_BASE_NOT_SETTING = "【%s】未设置公积金申报基数";

    @Autowired
    private MonthlyLedgerReposity reposity;

    @Autowired
    private MonthlyLedgerSummaryService summaryService;

    @Autowired
    private MandatoryBenefitsTypeService benefitsTypeService;

    @Autowired
    private CompanyBenefitService companyBenefitService;

    @Autowired
    private BasicStaffInfoService staffInfoService;

    @Autowired
    private SyncedBasicStaffInfoConvertor staffInfoConvertor;

    @Autowired
    private StaffBenefitService staffBenefitService;

    @Override
    public SimpleRestReposity<MonthlyLedger, Long> getSimpleRestReposity() {
        return reposity;
    }

    @Override
    public PageData<MonthlyLedgerVo> getLedgerList(String companyId, String userId, MonthlyLedgerRequest req) {
        validateLedgerDate(req.getLedgerYear(), req.getLedgerMonth());
        Specification<MonthlyLedger> whereClause = getLedgerListWhereClause(companyId, userId, req);
        Pageable pageable = getPageable(req.getPageNo(), req.getPageSize(), req.getSort());
        Page<MonthlyLedger> page = reposity.findAll(whereClause, pageable);
        return new PageData<>(page.getTotalPages(), page.getTotalElements(), getVos(page.getContent()));
    }

    private List<MonthlyLedgerVo> getVos(List<MonthlyLedger> ledgerList) {
        if (CollectionUtils.isEmpty(ledgerList)) {
            return Collections.emptyList();
        }
        List<MonthlyLedgerVo> result = Lists.newArrayListWithCapacity(ledgerList.size());
        ledgerList.forEach(item -> {
            MonthlyLedgerVo vo = new MonthlyLedgerVo();
            vo.setId(item.getId());
            vo.setDataSource(item.getDataSource());
            BenefitSnapshot socialInsurance = item.getStaffSocialInsurance();
            if (socialInsurance != null) {
                vo.setSiBase(socialInsurance.getBase());
            }
            vo.setSiCompanyBenefit(item.getSiCompanyBenefit());
            BenefitSnapshot houseFund = item.getStaffHouseFund();
            if (houseFund != null) {
                vo.setHfBase(houseFund.getBase());
            }
            vo.setHfCompanyBenefit(item.getHfCompanyBenefit());
            vo.setCells(item.getCells() != null ? item.getCells().getCells() : null);
            vo.setStaff(staffInfoConvertor.convertDtoToEntity(item.getStaff()));
            result.add(vo);
        });
        return result;
    }


    @Override
    @Transactional(rollbackFor = Exception.class)
    public LedgerResponse addLedgerStaff(String companyId, String userId,
                                         List<StaffBenefitVo> staffBenefits,
                                         Integer ledgerYear, Integer ledgerMonth) {
        ValidationUtils.rejectIfEmpty(staffBenefits, "", "员工数据不能为空");
        validateLedgerDate(ledgerYear, ledgerMonth);
        LedgerResponse ledgerResponse = new LedgerResponse();

        //**********************************处理员工台账数据**************************************/

        //获取公司目前全部可用的社保公积金方案
        List<CompanyBenefitVo> allValidCompanyBenefits = getAllValidCompanyBenefits(companyId, ledgerYear, ledgerMonth);

        List<MonthlyLedger> newStaffLedgerList = validateAddedStaffLedgers(userId, allValidCompanyBenefits, staffBenefits,
                ledgerYear, ledgerMonth, ledgerResponse);
        if (CollectionUtils.isEmpty(newStaffLedgerList)) {
            return ledgerResponse;
        }

        //设置新增的每个员工的单元格数据，保存
        Map<String, Map<String, DynamicRowCell>> staffCellValue = getStaffCellValue(newStaffLedgerList, allValidCompanyBenefits,
                ledgerYear, ledgerMonth, ledgerResponse);
        newStaffLedgerList.forEach(item -> item.setCells(new DynamicRow(staffCellValue.get(item.getStaff().getId()))));
        reposity.save(newStaffLedgerList);

        //**********************************处理台账概览数据**************************************/

        //获取台账概要信息：如果台账不存在，则创建
        MonthlyLedgerSummary ledgerSummary = summaryService.getLedgerSummary(companyId, userId, ledgerYear, ledgerMonth);
        if (ledgerSummary == null) {
            ledgerSummary = summaryService.createLedgerSummary(companyId, userId, ledgerYear, ledgerMonth);
        }

        //获取台账已有的全部员工
        List<MonthlyLedger> existedStaffLedgers = reposity.findAllByCompanyIdAndUserIdAndLedgerYearAndLedgerMonth(
                companyId, userId, ledgerYear, ledgerMonth);

        //获取当前台账中在使用的全部有效方案
        List<CompanyBenefitVo> allUsedValidCompanyBenefits = getAllUsedValidCompanyBenefits(allValidCompanyBenefits, existedStaffLedgers, newStaffLedgerList);

        //设置总的表头数据
        List<DynamicHeaderCell> sihfHeaderCells = getSIHFHeader(allUsedValidCompanyBenefits);
        ledgerSummary.setHeader(new DynamicHeader(sihfHeaderCells));

        //设置台账概要信息中的社保公积金人数
        setLedgerSummaryMemberCount(companyId, userId, ledgerYear, ledgerMonth, ledgerSummary);
        //设置台账合计数据
        setLedgerSummaryFooter(companyId, userId, ledgerYear, ledgerMonth, ledgerSummary);
        summaryService.saveLedgerSummary(ledgerSummary);
        return ledgerResponse;
    }

    private void setLedgerSummaryMemberCount(String companyId, String userId, Integer ledgerYear, Integer ledgerMonth, MonthlyLedgerSummary ledgerSummary) {
        int siMemberCount = reposity.countByCompanyIdAndUserIdAndLedgerYearAndLedgerMonthAndSiCompanyBenefitIsNotNull(
                companyId, userId, ledgerYear, ledgerMonth);
        ledgerSummary.getSiLedgerSummary().setSiMemberCount(siMemberCount);

        int hfMemberCount = reposity.countByCompanyIdAndUserIdAndLedgerYearAndLedgerMonthAndHfCompanyBenefitIsNotNull(
                companyId, userId, ledgerYear, ledgerMonth);
        ledgerSummary.getHfLedgerSummary().setHfMemberCount(hfMemberCount);
    }

    private void setLedgerSummaryFooter(String companyId, String userId, Integer ledgerYear, Integer ledgerMonth, MonthlyLedgerSummary ledgerSummary) {
        List<MonthlyLedger> allLedgers = reposity.findAllByCompanyIdAndUserIdAndLedgerYearAndLedgerMonth(
                companyId, userId, ledgerYear, ledgerMonth);
        reCalculateLedgerSummaryFooter(allLedgers, ledgerSummary);
    }

    private void reCalculateLedgerSummaryFooter(List<MonthlyLedger> allStaffLedgers, MonthlyLedgerSummary ledgerSummary) {
        if (CollectionUtils.isEmpty(allStaffLedgers)) {
            ledgerSummary.setFooter(null);
            ledgerSummary.getSiLedgerSummary().setSiCost(BigDecimal.ZERO);
            ledgerSummary.getHfLedgerSummary().setHfCost(BigDecimal.ZERO);
            return;
        }
        Map<String, BigDecimal> footerCellIdValueMap = getInitializedFooterCellIdValueMap(ledgerSummary.getHeader());
        if (CollectionUtils.isEmpty(footerCellIdValueMap)) {
            ledgerSummary.getSiLedgerSummary().setSiCost(BigDecimal.ZERO);
            ledgerSummary.getHfLedgerSummary().setHfCost(BigDecimal.ZERO);
            ledgerSummary.setFooter(null);
            return;
        }
        BigDecimal siTotal = BigDecimal.ZERO;
        BigDecimal hfTotal = BigDecimal.ZERO;
        for (MonthlyLedger ledger : allStaffLedgers) {

            DynamicRow row = ledger.getCells();
            if (row == null || CollectionUtils.isEmpty(row.getCells())) {
                return;
            }
            Map<String, DynamicRowCell> cells = row.getCells();
            for (Map.Entry<String, DynamicRowCell> cell : cells.entrySet()) {
                String cellId = cell.getKey();
                DynamicRowCell value = cell.getValue();
                if (footerCellIdValueMap.containsKey(cellId)) {
                    String cellValue = value.getCellValue();
                    BigDecimal cellValueBigDecimal = BigDecimal.ZERO;
                    try {
                        cellValueBigDecimal = new BigDecimal(cellValue);
                    } catch (Exception e) {
                        //do nothing
                    }
                    BigDecimal add = footerCellIdValueMap.get(cellId).add(cellValueBigDecimal);
                    footerCellIdValueMap.put(cellId, add);
                    if (!cellId.contains(SIHFConstants.CELL_ID_FLAG_TOTAL)) {
                        if (cellId.startsWith(SIHFConstants.CELL_ID_FLAG_SI)) {
                            siTotal = siTotal.add(cellValueBigDecimal);
                        } else if (cellId.startsWith(SIHFConstants.CELL_ID_FLAG_HF)) {
                            hfTotal = hfTotal.add(cellValueBigDecimal);
                        }
                    }
                }
            }
        }

        ledgerSummary.getSiLedgerSummary().setSiCost(siTotal);
        ledgerSummary.getHfLedgerSummary().setHfCost(hfTotal);

        DynamicRow footerRow = new DynamicRow(Maps.newHashMap());
        footerCellIdValueMap.forEach((cellId, value) -> footerRow.getCells().put(cellId, new DynamicRowCell(value.toString())));
        footerRow.getCells().put(SIHFConstants.STAFF_NAME_CELL_ID, new DynamicRowCell("合计"));
        DynamicFooter footer = new DynamicFooter(footerRow);
        ledgerSummary.setFooter(footer);
    }

    private Map<String, BigDecimal> getInitializedFooterCellIdValueMap(DynamicHeader header) {
        if (header == null || CollectionUtils.isEmpty(header.getHeader())) {
            return Maps.newHashMap();
        }
        List<DynamicHeaderCell> headerCells = header.getHeader();
        Map<String, BigDecimal> result = Maps.newHashMap();
        headerCells.forEach(item -> {
            String cellId = item.getCellId();
            if (SIHFConstants.STAFF_NAME_CELL_ID.equals(cellId)
                    || SIHFConstants.ID_CARD_NO_CELL_ID.equals(cellId)
                    || SIHFConstants.DEPARTMENT_NAME_CELL_ID.equals(cellId)
                    || SIHFConstants.DATA_SOURCE_CELL_NAME.equals(cellId)
                    || SIHFConstants.SI_BENEFIT_NAME_CELL_NAME.equals(cellId)
                    || SIHFConstants.HF_BENEFIT_NAME_CELL_NAME.equals(cellId)) {
                return;
            }
            result.put(cellId, BigDecimal.ZERO);
        });
        return result;

    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public LedgerResponse calculateLedgers(String companyId, String userId, Integer ledgerYear, Integer ledgerMonth) {
        validateLedgerDate(ledgerYear, ledgerMonth);
        //获取台账概要信息：如果台账不存在，则创建
        MonthlyLedgerSummary ledgerSummary = summaryService.getLedgerSummary(companyId, userId, ledgerYear, ledgerMonth);
        ValidationUtils.rejectIfEmpty(ledgerSummary, "", "月度台账不存在，请刷新重试");

        //获取台账的全部员工（包含即将添加的员工）
        List<MonthlyLedger> allStaffLedgers = reposity.findAllByCompanyIdAndUserIdAndLedgerYearAndLedgerMonth(companyId, userId, ledgerYear, ledgerMonth);
        ValidationUtils.rejectIfEmpty(allStaffLedgers, "", "台账中不存在员工，无法核算");

        LedgerResponse ledgerResponse = new LedgerResponse();

        //获取公司目前全部可用的社保公积金方案
        List<CompanyBenefitVo> allValidCompanyBenefits = getAllValidCompanyBenefits(companyId, ledgerYear, ledgerMonth);

        //更新台账的快照数据
        updateStaffLedgerSnapShotData(companyId, allValidCompanyBenefits, allStaffLedgers, ledgerYear, ledgerMonth, ledgerResponse);

        //重新设置每个员工的单元格数据，保存
        Map<String, Map<String, DynamicRowCell>> staffCellValue = getStaffCellValue(allStaffLedgers, allValidCompanyBenefits,
                ledgerYear, ledgerMonth, ledgerResponse);
        allStaffLedgers.forEach(item -> {
            item.setDataSource(LedgerItemDataSource.CALC_ONLINE);
            Map<String, DynamicRowCell> cellMap = staffCellValue.get(item.getStaff().getId());
            item.setCells(new DynamicRow(cellMap));
        });
        reposity.save(allStaffLedgers);

        //获取当前台账中在使用的全部有效方案
        List<CompanyBenefitVo> allUsedValidCompanyBenefits = getAllUsedValidCompanyBenefits(allValidCompanyBenefits, allStaffLedgers, Collections.emptyList());

        //设置总的表头数据
        List<DynamicHeaderCell> sihfHeaderCells = getSIHFHeader(allUsedValidCompanyBenefits);
        ledgerSummary.setHeader(new DynamicHeader(sihfHeaderCells));
        //设置台账合计数据
        setLedgerSummaryFooter(companyId, userId, ledgerYear, ledgerMonth, ledgerSummary);
        summaryService.saveLedgerSummary(ledgerSummary);
        return ledgerResponse;
    }

    /**
     * 重新核算时，更新快照数据
     *
     * @param companyId
     * @param allValidCompanyBenefits
     * @param allStaffLedgers
     * @param ledgerYear
     * @param ledgerMonth
     * @param ledgerResponse
     */
    private void updateStaffLedgerSnapShotData(String companyId, List<CompanyBenefitVo> allValidCompanyBenefits,
                                               List<MonthlyLedger> allStaffLedgers,
                                               Integer ledgerYear, Integer ledgerMonth,
                                               LedgerResponse ledgerResponse) {
        List<String> staffIds = Lists.newArrayListWithCapacity(allStaffLedgers.size());
        allStaffLedgers.forEach(item -> staffIds.add(item.getStaff().getId()));

        List<StaffBenefitVo> allStaffBenefits = staffBenefitService.getAllStaffBenefitInclude(companyId, staffIds);
        Map<String, StaffBenefitVo> staffIdStaffBenefitMap = Maps.newHashMap();
        allStaffBenefits.forEach(item -> staffIdStaffBenefitMap.put(item.getStaff().getId(), item));

        Date ledgerDate = getLedgerDate(ledgerYear, ledgerMonth);
        //方案id
        List<Long> validIds = allValidCompanyBenefits.stream().map(CompanyBenefitVo::getId).collect(Collectors.toList());

        allStaffLedgers.forEach(item -> {
            String staffName = item.getStaff().getStaffName();
            StaffBenefitVo benefitVo = staffIdStaffBenefitMap.get(item.getStaff().getId());

            BenefitVo socialInsurance = benefitVo.getSocialInsurance();
            boolean siBenefitValid = isSIHFBenefitValid(validIds, socialInsurance);
            boolean staffSIDateValid = isStaffSIHFBenefitDateValid(socialInsurance, ledgerDate);
            if (!siBenefitValid) {
                ledgerResponse.addErrorInfo(String.format(SI_INVALID, staffName));
            }
            if (!staffSIDateValid) {
                ledgerResponse.addErrorInfo(String.format(SI_DATE_INVALID, staffName));
            }

            BenefitVo houseFund = benefitVo.getHouseFund();
            boolean hfBenefitValid = isSIHFBenefitValid(validIds, houseFund);
            boolean staffHFDateValid = isStaffSIHFBenefitDateValid(houseFund, ledgerDate);

            if (!hfBenefitValid) {
                ledgerResponse.addErrorInfo(String.format(HF_INVALID, staffName));
            }
            if (!staffHFDateValid) {
                ledgerResponse.addErrorInfo(String.format(HF_DATE_INVALID, staffName));
            }

            boolean siValid = siBenefitValid && staffSIDateValid;
            boolean hfValid = hfBenefitValid && staffHFDateValid;
            if (!siValid && !hfValid) {
                item.setStaffSocialInsurance(null);
                item.setSiCompanyBenefit(null);
                item.setStaffHouseFund(null);
                item.setHfCompanyBenefit(null);
                return;
            }

            if (siValid) {
                CompanyBenefitSnapshot siCompanyBenefit = new CompanyBenefitSnapshot();
                siCompanyBenefit.setId(socialInsurance.getCompanyBenefitId());
                siCompanyBenefit.setName(socialInsurance.getCompanyBenefitName());
                item.setSiCompanyBenefit(siCompanyBenefit);
                if (Boolean.TRUE.equals(socialInsurance.getCalculatable())) {
                    BenefitSnapshot siStaffBenefit = new BenefitSnapshot();
                    siStaffBenefit.setStartOn(socialInsurance.getStartOn());
                    siStaffBenefit.setEndOn(socialInsurance.getEndOn());
                    siStaffBenefit.setBase(socialInsurance.getBase());
                    siStaffBenefit.setCalculatable(socialInsurance.getCalculatable());
                    item.setStaffSocialInsurance(siStaffBenefit);
                } else {
                    ledgerResponse.addErrorInfo(String.format(SI_NOT_CALCULATE_INFO, staffName));
                    item.setStaffSocialInsurance(null);
                }
            } else {
                item.setStaffSocialInsurance(null);
                item.setSiCompanyBenefit(null);
            }

            if (hfValid) {
                CompanyBenefitSnapshot hfCompanyBenefit = new CompanyBenefitSnapshot();
                hfCompanyBenefit.setId(houseFund.getCompanyBenefitId());
                hfCompanyBenefit.setName(houseFund.getCompanyBenefitName());
                item.setHfCompanyBenefit(hfCompanyBenefit);
                if (Boolean.TRUE.equals(houseFund.getCalculatable())) {
                    BenefitSnapshot hfStaffBenefit = new BenefitSnapshot();
                    hfStaffBenefit.setStartOn(houseFund.getStartOn());
                    hfStaffBenefit.setEndOn(houseFund.getEndOn());
                    hfStaffBenefit.setBase(houseFund.getBase());
                    hfStaffBenefit.setCalculatable(houseFund.getCalculatable());
                    item.setStaffHouseFund(hfStaffBenefit);
                } else {
                    ledgerResponse.addErrorInfo(String.format(HF_NOT_CALCULATE_INFO, staffName));
                    item.setStaffHouseFund(null);
                }
            } else {
                item.setStaffHouseFund(null);
                item.setHfCompanyBenefit(null);
            }
        });
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void batchDelete(List<Long> ids) {
        Iterable<MonthlyLedger> dtos = reposity.findAll(ids);
        reposity.delete(dtos);

        Iterator<MonthlyLedger> iterator = dtos.iterator();
        List<MonthlyLedger> deletedDtos = Lists.newArrayList();
        while (iterator.hasNext()) {
            deletedDtos.add(iterator.next());
        }
        ValidationUtils.rejectIfEmpty(deletedDtos, "", "要删除的员工在台账中不存在，请刷新后重试");
        MonthlyLedger ledger = deletedDtos.get(0);
        String companyId = ledger.getCompanyId();
        String userId = ledger.getUserId();
        Integer ledgerYear = ledger.getLedgerYear();
        Integer ledgerMonth = ledger.getLedgerMonth();
        MonthlyLedgerSummary ledgerSummary = summaryService.getLedgerSummary(companyId, userId, ledgerYear, ledgerMonth);
        ValidationUtils.rejectIfEmpty(ledgerSummary, "", LEDGER_NOT_EXISTS_ERROR);
        setLedgerSummaryMemberCount(companyId, userId, ledgerYear, ledgerMonth, ledgerSummary);
        //设置台账合计数据
        setLedgerSummaryFooter(companyId, userId, ledgerYear, ledgerMonth, ledgerSummary);
        summaryService.saveLedgerSummary(ledgerSummary);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteLedger(String companyId, String userId,
                             Integer ledgerYear, Integer ledgerMonth) {
        validateLedgerDate(ledgerYear, ledgerMonth);
        reposity.deleteByCompanyIdAndUserIdAndLedgerYearAndLedgerMonth(companyId, userId, ledgerYear, ledgerMonth);
    }

    @Override
    public void deleteLedgerStaff(String companyId, String userId,
                                  Integer ledgerYear, Integer ledgerMonth, List<String> staffIds) {
        validateLedgerDate(ledgerYear, ledgerMonth);
        List<MonthlyLedger> deletedDtos = reposity.findAllByCompanyIdAndUserIdAndLedgerYearAndLedgerMonthAndStaffIdIn(companyId, userId, ledgerYear, ledgerMonth, staffIds);
        ValidationUtils.rejectIfEmpty(deletedDtos, "", "要删除的员工在台账中不存在，请刷新后重试");
        reposity.delete(deletedDtos);

        MonthlyLedgerSummary ledgerSummary = summaryService.getLedgerSummary(companyId, userId, ledgerYear, ledgerMonth);
        ValidationUtils.rejectIfEmpty(ledgerSummary, "", LEDGER_NOT_EXISTS_ERROR);
        setLedgerSummaryMemberCount(companyId, userId, ledgerYear, ledgerMonth, ledgerSummary);
        //设置台账合计数据
        setLedgerSummaryFooter(companyId, userId, ledgerYear, ledgerMonth, ledgerSummary);
        summaryService.saveLedgerSummary(ledgerSummary);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void saveLedgerCells(String userId, Map<Long, Map<String, DynamicRowCell>> ledgerCells) {
        Iterable<MonthlyLedger> monthlyLedgers = reposity.findAll(ledgerCells.keySet());
        Iterator<MonthlyLedger> iterator = monthlyLedgers.iterator();
        List<MonthlyLedger> savedDatas = Lists.newArrayList();
        while (iterator.hasNext()) {
            MonthlyLedger next = iterator.next();
            //数据验证
            if (userId.equals(next.getUserId())) {
                savedDatas.add(next);
            }
        }
        if (CollectionUtils.isEmpty(savedDatas)) {
            ValidationUtils.reject("", "要保存的数据不存在，请刷新后重试");
        }
        savedDatas.forEach(ledger -> ledger.setCells(new DynamicRow(ledgerCells.get(ledger.getId()))));
        reposity.save(savedDatas);

        MonthlyLedger monthlyLedger = savedDatas.get(0);
        String companyId = monthlyLedger.getCompanyId();
        Integer ledgerYear = monthlyLedger.getLedgerYear();
        Integer ledgerMonth = monthlyLedger.getLedgerMonth();
        MonthlyLedgerSummary ledgerSummary = summaryService.getLedgerSummary(companyId, userId, ledgerYear, ledgerMonth);
        ValidationUtils.rejectIfEmpty(ledgerSummary, "", LEDGER_NOT_EXISTS_ERROR);
        //设置台账合计数据
        setLedgerSummaryFooter(companyId, userId, ledgerYear, ledgerMonth, ledgerSummary);
        summaryService.saveLedgerSummary(ledgerSummary);
    }

    @Override
    public List<DynamicHeaderCell> getSIHFTotalHeaderCell() {
        List<BenefitTypeVo> currentEnabledTypes = benefitsTypeService.getEnabledTypes();
        if (CollectionUtils.isEmpty(currentEnabledTypes)) {
            return Collections.emptyList();
        }
        return getHeaderCells(currentEnabledTypes);
    }

    @Override
    public Map<String, Map<String, Double>> getStaffLedger(String companyId, String userId,
                                                           Integer ledgerYear, Integer ledgerMonth,
                                                           List<String> staffIds) {
        validateLedgerDate(ledgerYear, ledgerMonth);
        List<MonthlyLedger> ledgers = reposity.findAllByCompanyIdAndUserIdAndLedgerYearAndLedgerMonthAndStaffIdIn(
                companyId, userId, ledgerYear, ledgerMonth, staffIds);
        if (CollectionUtils.isEmpty(ledgers)) {
            return Maps.newHashMap();
        }
        Map<String, Map<String, Double>> result = Maps.newHashMap();
        ledgers.forEach(item -> {
            if (item.getCells() == null || CollectionUtils.isEmpty(item.getCells().getCells())) {
                return;
            }
            Map<String, DynamicRowCell> cells = item.getCells().getCells();
            Map<String, Double> cellMap = Maps.newHashMap();
            cells.forEach((key, value) -> cellMap.put(key,
                    StringUtils.isNotBlank(value.getCellValue()) ? Double.valueOf(value.getCellValue()) : null));
            result.put(item.getStaff().getId(), cellMap);
        });
        return result;
    }

    @Override
    public List<String> getStaffIds(String companyId, String userId, Integer ledgerYear, Integer ledgerMonth) {
        validateLedgerDate(ledgerYear, ledgerMonth);
        return reposity.findAllStaffIds(companyId, userId, ledgerYear, ledgerMonth);
    }

    @Override
    public ExportParams<List<Object>> getExportData(String companyId, String userId, MonthlyLedgerRequest req, Map<Long, String> departmentFullNames) {
        Integer ledgerYear = req.getLedgerYear();
        Integer ledgerMonth = req.getLedgerMonth();
        validateLedgerDate(ledgerYear, ledgerMonth);

        MonthlyLedgerSummary ledgerSummary = summaryService.getLedgerSummary(companyId, userId, ledgerYear, ledgerMonth);
        ValidationUtils.rejectIfEmpty(ledgerSummary, "", "月度台账不存在，请刷新重试");

        ExportParams<List<Object>> exportParams = new ExportParams();

        Map<String, String> headerMap = getExcelHeaderMap(ledgerSummary);
        exportParams.setHeaderMap(headerMap);

        List<List<Object>> excelData = getExcelData(headerMap, departmentFullNames, ledgerSummary.getFooter(), companyId, userId, req);
        exportParams.setRowDatas(excelData);

        return exportParams;
    }

    @Override
    public void downloadTemplate(String companyId, Integer ledgerYear, Integer ledgerMonth, ServletOutputStream outputStream) {
        validateLedgerDate(ledgerYear, ledgerMonth);
        //获取月度下的有效方案
        List<CompanyBenefitVo> enabledCompanyBenefits = companyBenefitService.getEnabledCompanyBenefits(companyId);
        correctCompanyBenefits(enabledCompanyBenefits, ledgerYear, ledgerMonth);

        BenefitNameInfo benefitNameInfo = getGroupedBenefitName(enabledCompanyBenefits);
        validateCompanyBenefits(benefitNameInfo);

        ExportParams exportParams = new ExportParams();
        List<DynamicHeaderCell> sihfHeaderCells = getSIHFHeader(enabledCompanyBenefits);
        Map<String, String> headerMap = getTemplateWholeHeader(sihfHeaderCells, benefitNameInfo);
        exportParams.setHeaderMap(headerMap);

        Map<String, ExcelCellStyle> headerStyleMap = getHeaderStyleMap();
        exportParams.setHeaderStyleMap(headerStyleMap);

        Map<String, CellComment> headerCommentMap = getHeaderCommentMap(benefitNameInfo);
        exportParams.setHeaderCommentMap(headerCommentMap);

        Map<String, List<String>> dropDownsMap = getDropDownsMap(benefitNameInfo);
        exportParams.setDropDownsMap(dropDownsMap);

        ExcelUtil.exportExcel(exportParams, outputStream);
        try {
            outputStream.close();
        } catch (IOException e) {
            ValidationUtils.reject("", "模板下载失败,请刷新重试");
            logger.info(e.getMessage());
        }
    }

    @Override
    public CustomSpreadSheetValidateResult validateExcelData(String companyId, String userId,
                                                             Integer ledgerYear, Integer ledgerMonth,
                                                             SpreadSheetValidatorRequest validatorRequest) {
        validateLedgerDate(ledgerYear, ledgerMonth);

        CustomSpreadSheetValidateResult result = new CustomSpreadSheetValidateResult();
        List<SpreadSheetRow> excelDatas = validatorRequest.getSpreadSheetDatas();

        //校验表头
        checkHeaders(excelDatas.get(0));

        //******************************************校验员工数据********************************************/
        //获取公司目前全部可用的社保公积金方案
        List<CompanyBenefitVo> allValidCompanyBenefits = getAllValidCompanyBenefits(companyId, ledgerYear, ledgerMonth);
        ValidationUtils.rejectIfEmpty(allValidCompanyBenefits, "", "当前月无可用的社保与公积金方案，请先进行方案设置");

        Map<String, SpreadSheetRow> staffIdExcelRowMap = excelDatas.stream()
                .collect(Collectors.toMap(SpreadSheetRow::getStaffId, row -> row));
        //校验导入数据的方案名称是否有效
        staffIdExcelRowMap = checkCompanyBenefitNames(allValidCompanyBenefits, staffIdExcelRowMap, result);
        //获取所导入员工的社保公积金方案信息
        List<StaffBenefitVo> staffBenefitVos = staffBenefitService.getAllStaffBenefitInclude(companyId, Lists.newArrayList(staffIdExcelRowMap.keySet()));
        //校验导入员工的缴纳时间是否有效
        staffIdExcelRowMap = checkStaffBenefitDate(ledgerYear, ledgerMonth, staffBenefitVos, staffIdExcelRowMap, result);

        if (CollectionUtils.isEmpty(staffIdExcelRowMap)) {
            return result;
        }
        //获取台账中已有的全部员工
        List<MonthlyLedger> existedStaffLedgers = reposity.findAllByCompanyIdAndUserIdAndLedgerYearAndLedgerMonth(
                companyId, userId, ledgerYear, ledgerMonth);
        //将可导入的行数据转dto
        List<MonthlyLedger> importedStaffLedgers = convertExcelRows2LedgerDtos(userId, ledgerYear, ledgerMonth,
                existedStaffLedgers, allValidCompanyBenefits, staffBenefitVos, staffIdExcelRowMap, result);
        try {
            result.setConvertResult(SpreadSheetConvertResultUtil.ObjectToByte(importedStaffLedgers));
        } catch (IOException e) {
            logger.error(e.toString());
            ValidationUtils.reject(null, "可导入的正确数据保存失败");
        }
        return result;
    }

    @Override
    public void importExcel(String companyId, String userId,
                            Integer ledgerYear, Integer ledgerMonth,
                            ImportRequest importRequest) {
        validateLedgerDate(ledgerYear, ledgerMonth);

        //******************************************保存员工台账数据********************************************/
        List<MonthlyLedger> importedStaffLedgers = null;
        try {
            importedStaffLedgers = importRequest.getConvertResultList();
        } catch (Exception e) {
            logger.error(e.toString());
            ValidationUtils.reject(null, "正确数据导入失败");
        }

        reposity.save(importedStaffLedgers);

        //******************************************保存台账概要数据********************************************/

        //获取台账概要信息：如果台账不存在，则创建
        MonthlyLedgerSummary ledgerSummary = summaryService.getLedgerSummary(companyId, userId, ledgerYear, ledgerMonth);
        if (ledgerSummary == null) {
            ledgerSummary = summaryService.createLedgerSummary(companyId, userId, ledgerYear, ledgerMonth);
        }
        //获取公司目前全部可用的社保公积金方案
        List<CompanyBenefitVo> allValidCompanyBenefits = getAllValidCompanyBenefits(companyId, ledgerYear, ledgerMonth);
        //获取台账中已有的全部员工
        List<MonthlyLedger> existedStaffLedgers = reposity.findAllByCompanyIdAndUserIdAndLedgerYearAndLedgerMonth(
                companyId, userId, ledgerYear, ledgerMonth);
        //获取当前台账中在使用的全部有效方案
        List<CompanyBenefitVo> allUsedValidCompanyBenefits = getAllUsedValidCompanyBenefits(allValidCompanyBenefits, existedStaffLedgers, importedStaffLedgers);
        //设置总的表头数据
        List<DynamicHeaderCell> sihfHeaderCells = getSIHFHeader(allUsedValidCompanyBenefits);
        ledgerSummary.setHeader(new DynamicHeader(sihfHeaderCells));

        //设置台账概要信息中的社保公积金人数，保存
        setLedgerSummaryMemberCount(companyId, userId, ledgerYear, ledgerMonth, ledgerSummary);
        //设置台账合计数据
        setLedgerSummaryFooter(companyId, userId, ledgerYear, ledgerMonth, ledgerSummary);
        summaryService.saveLedgerSummary(ledgerSummary);
    }

    private void addRowLog(CustomSpreadSheetValidateResult result, Integer rowNum, Integer colNum, String log) {
        if (result.getErrors() == null) {
            result.setErrors(Lists.newArrayList());
        }
        SpreadSheetValidatorError error = new SpreadSheetValidatorError();
        error.setRowNum(rowNum);
        error.setColNum(colNum);
        error.setMsg(log);
        result.getErrors().add(error);
    }

    private void checkHeaders(SpreadSheetRow row) {
        List<SpreadSheetCell> cells = row.getCells();
        Set<String> headerName = cells.stream().map(SpreadSheetCell::getHeaderName).collect(Collectors.toSet());
        if (!headerName.contains(SIHFConstants.SI_BENEFIT_NAME_CELL_NAME)
                && !headerName.contains(SIHFConstants.HF_BENEFIT_NAME_CELL_NAME)) {
            ValidationUtils.reject(null, "至少要有【社保方案名称】或【公积金方案名称】中的一列！");
        }
    }

    /**
     * 校验方案名称
     *  @param allValidCompanyBenefits
     * @param staffIdExcelRowMap
     * @param result
     */
    private Map<String, SpreadSheetRow> checkCompanyBenefitNames(List<CompanyBenefitVo> allValidCompanyBenefits,
                                                                 Map<String, SpreadSheetRow> staffIdExcelRowMap,
                                                                 CustomSpreadSheetValidateResult result) {

        //方案名称 id
        Map<String, Long> benefitNameIdMap = allValidCompanyBenefits.stream().collect(
                Collectors.toMap(CompanyBenefitVo::getName, CompanyBenefitVo::getId));

        Map<String, SpreadSheetRow> validStaffIdExcelRowMap = Maps.newLinkedHashMap();

        staffIdExcelRowMap.forEach((staffId, row) -> {
            List<SpreadSheetCell> cells = row.getCells();
            if (CollectionUtils.isEmpty(cells)) {
                return;
            }
            Map<String, SpreadSheetCell> cellMap = cells.stream()
                    .collect(Collectors.toMap(SpreadSheetCell::getHeaderName, cell -> cell));
            //校验方案名称(表头校验保证了siCompanyBenefitNameCell与siCompanyBenefitNameCell至少一项不为空)
            SpreadSheetCell siCompanyBenefitNameCell = cellMap.get(SIHFConstants.SI_BENEFIT_NAME_CELL_NAME);
            SpreadSheetCell hfCompanyBenefitNameCell = cellMap.get(SIHFConstants.HF_BENEFIT_NAME_CELL_NAME);

            if (isBenefitNameInvalid(result, benefitNameIdMap, siCompanyBenefitNameCell, hfCompanyBenefitNameCell)) {
                return;
            }
            validStaffIdExcelRowMap.put(staffId, row);
        });

        return validStaffIdExcelRowMap;
    }

    private boolean isBenefitNameInvalid(CustomSpreadSheetValidateResult result, Map<String, Long> benefitNameIdMap,
                                         SpreadSheetCell siCompanyBenefitNameCell, SpreadSheetCell hfCompanyBenefitNameCell) {
        if (siCompanyBenefitNameCell == null) {
            if (hfCompanyBenefitNameCell.getCellValue() == null) {
                addRowLog(result, hfCompanyBenefitNameCell.getRowNum(), hfCompanyBenefitNameCell.getColNum(), "公积金方案名称不能为空");
                return true;
            }
        } else if (hfCompanyBenefitNameCell == null) {
            if (siCompanyBenefitNameCell.getCellValue() == null) {
                addRowLog(result, siCompanyBenefitNameCell.getRowNum(), siCompanyBenefitNameCell.getColNum(), "社保方案名称不能为空");
                return true;
            }
        } else {
            if (siCompanyBenefitNameCell.getCellValue() == null
                    && hfCompanyBenefitNameCell.getCellValue() == null) {
                addRowLog(result, siCompanyBenefitNameCell.getRowNum(), siCompanyBenefitNameCell.getColNum(), "社保方案名称与公积金方案名称至少一项不能为空");
                return true;
            }
        }

        if (siCompanyBenefitNameCell != null
                && siCompanyBenefitNameCell.getCellValue() != null
                && !benefitNameIdMap.containsKey(siCompanyBenefitNameCell.getCellValue().toString())) {
            addRowLog(result, siCompanyBenefitNameCell.getRowNum(), siCompanyBenefitNameCell.getColNum(), "社保方案无效");
            return true;
        }
        if (hfCompanyBenefitNameCell != null
                && hfCompanyBenefitNameCell.getCellValue() != null
                && !benefitNameIdMap.containsKey(hfCompanyBenefitNameCell.getCellValue().toString())) {
            addRowLog(result, hfCompanyBenefitNameCell.getRowNum(), hfCompanyBenefitNameCell.getColNum(), "公积金方案无效");
            return true;
        }
        return false;
    }

    /**
     * 校验员工缴纳起始月、终止月数据
     *  @param ledgerYear
     * @param ledgerMonth
     * @param staffBenefitVos
     * @param staffIdExcelRowMap
     * @param result
     */
    private Map<String, SpreadSheetRow> checkStaffBenefitDate(Integer ledgerYear,
                                                              Integer ledgerMonth,
                                                              List<StaffBenefitVo> staffBenefitVos,
                                                              Map<String, SpreadSheetRow> staffIdExcelRowMap,
                                                              CustomSpreadSheetValidateResult result) {
        Date ledgerDate = getLedgerDate(ledgerYear, ledgerMonth);
        Map<String, SpreadSheetRow> validStaffIdExcelRowMap = Maps.newLinkedHashMap();

        Map<String, StaffBenefitVo> staffBenefitMap = staffBenefitVos.stream().collect(Collectors.toMap(s -> s.getStaff().getId(), s -> s));
        staffIdExcelRowMap.forEach((staffId, row) -> {
            if (!staffBenefitMap.containsKey(staffId)) {
                addRowLog(result, row.getRowNum(), null, "员工还未关联社保公积金方案数据");
                return;
            }
            StaffBenefitVo benefitVo = staffBenefitMap.get(staffId);
            BenefitVo socialInsurance = benefitVo.getSocialInsurance();
            boolean siDateValid = isStaffSIHFBenefitDateValid(socialInsurance, ledgerDate);
            BenefitVo houseFund = benefitVo.getHouseFund();
            boolean hfDateValid = isStaffSIHFBenefitDateValid(houseFund, ledgerDate);
            if (!siDateValid && !hfDateValid) {
                addRowLog(result, row.getRowNum(), null, "员工社保和公积金的起始缴纳月、最后缴纳月均不在台账月度范围内");
                return;
            }

            validStaffIdExcelRowMap.put(staffId, row);
        });

        return validStaffIdExcelRowMap;
    }

    private List<MonthlyLedger> convertExcelRows2LedgerDtos(String userId, int ledgerYear, int ledgerMonth,
                                                            List<MonthlyLedger> existedLedgers,
                                                            List<CompanyBenefitVo> allValidCompanyBenefits,
                                                            List<StaffBenefitVo> existedStaffBenefits,
                                                            Map<String, SpreadSheetRow> staffIdExcelRowMap,
                                                            CustomSpreadSheetValidateResult result) {
        Date ledgerDate = getLedgerDate(ledgerYear, ledgerMonth);
        Map<String, Long> benefitNameIdMap = allValidCompanyBenefits.stream().collect(
                Collectors.toMap(CompanyBenefitVo::getName, CompanyBenefitVo::getId));
        Collection<Long> companyBenefitIds = benefitNameIdMap.values();

        //获取系统总的支持的表头列
        List<DynamicHeaderCell> sihfTotalHeaderCell = getSIHFTotalHeaderCell();
        Map<String, String> totalCellNameAndIdMap = Maps.newHashMap();
        sihfTotalHeaderCell.forEach(item -> totalCellNameAndIdMap.put(item.getCellName(), item.getCellId()));

        Map<String, MonthlyLedger> existedStaffIdLedgerMap = Maps.newHashMap();
        existedLedgers.forEach(item -> existedStaffIdLedgerMap.put(item.getStaff().getId(), item));

        Map<String, StaffBenefitVo> existedStaffIdStaffBenefitMap = Maps.newHashMap();
        existedStaffBenefits.forEach(item -> existedStaffIdStaffBenefitMap.put(item.getStaff().getId(), item));

        List<MonthlyLedger> resultDtos = Lists.newArrayList();
        staffIdExcelRowMap.forEach((staffId, row) -> {
            MonthlyLedger dto = existedStaffIdLedgerMap.get(staffId);
            if (dto == null) {
                dto = new MonthlyLedger();
                dto.setLedgerYear(ledgerYear);
                dto.setLedgerMonth(ledgerMonth);
            }
                SyncedBasicStaffInfo staff = new SyncedBasicStaffInfo();
                staff.setId(staffId);
                dto.setStaff(staff);
            if (dto.getCells() == null || dto.getCells().getCells() == null) {
                dto.setCells(new DynamicRow(Maps.newHashMap()));
            }
            dto.setDataSource(LedgerItemDataSource.IMPORT_OFFLINE);
            dto.setUserId(userId);

            updateDto(result, ledgerDate, benefitNameIdMap, companyBenefitIds, totalCellNameAndIdMap, existedStaffIdStaffBenefitMap, staffId, row, dto);
            resultDtos.add(dto);
        });
        return resultDtos;
    }

    private void updateDto(CustomSpreadSheetValidateResult result, Date ledgerDate,
                           Map<String, Long> benefitNameIdMap,
                           Collection<Long> companyBenefitIds,
                           Map<String, String> totalCellNameAndIdMap,
                           Map<String, StaffBenefitVo> existedStaffIdStaffBenefitMap,
                           String staffId, SpreadSheetRow row,
                           MonthlyLedger dto) {
        Map<String, DynamicRowCell> rowCellMap = dto.getCells().getCells();
        List<SpreadSheetCell> cells = row.getCells();
        for (SpreadSheetCell cell : cells) {
            String cellName = cell.getHeaderName();
            Object cellValue = cell.getCellValue();
            if (!needCheck(cellName, cellValue)) {
                continue;
            }
            String value = cellValue.toString();
            if (SIHFConstants.SI_BENEFIT_NAME_CELL_NAME.equals(cellName)) {
                if (benefitNameIdMap.containsKey(value)) {
                    CompanyBenefitSnapshot siCompanyBenefit = new CompanyBenefitSnapshot();
                    siCompanyBenefit.setId(benefitNameIdMap.get(value));
                    siCompanyBenefit.setName(value);
                    dto.setSiCompanyBenefit(siCompanyBenefit);
                }
            } else if (SIHFConstants.HF_BENEFIT_NAME_CELL_NAME.equals(cellName)) {
                if (benefitNameIdMap.containsKey(value)) {
                    CompanyBenefitSnapshot hfCompanyBenefit = new CompanyBenefitSnapshot();
                    hfCompanyBenefit.setId(benefitNameIdMap.get(value));
                    hfCompanyBenefit.setName(value);
                    dto.setHfCompanyBenefit(hfCompanyBenefit);
                }
            } else if (totalCellNameAndIdMap.containsKey(cellName)) {
                checkDoubleValue(result, totalCellNameAndIdMap, rowCellMap, cell, cellName, value);
            }
        }

        if (existedStaffIdStaffBenefitMap.containsKey(staffId)) {
            StaffBenefitVo benefitVo = existedStaffIdStaffBenefitMap.get(staffId);
            BenefitVo socialInsurance = benefitVo.getSocialInsurance();
            if (isSIHFValid(companyBenefitIds, socialInsurance, ledgerDate)) {
                BenefitSnapshot si = new BenefitSnapshot();
                si.setBase(socialInsurance.getBase());
                si.setStartOn(socialInsurance.getStartOn());
                si.setEndOn(socialInsurance.getEndOn());
                si.setCalculatable(socialInsurance.getCalculatable());
                dto.setStaffSocialInsurance(si);
            }
            BenefitVo houseFund = benefitVo.getHouseFund();
            if (isSIHFValid(companyBenefitIds, houseFund, ledgerDate)) {
                BenefitSnapshot hf = new BenefitSnapshot();
                hf.setBase(houseFund.getBase());
                hf.setStartOn(houseFund.getStartOn());
                hf.setEndOn(houseFund.getEndOn());
                hf.setCalculatable(houseFund.getCalculatable());
                dto.setStaffHouseFund(hf);
            }
        }
    }

    private void checkDoubleValue(CustomSpreadSheetValidateResult result,
                                  Map<String, String> totalCellNameAndIdMap,
                                  Map<String, DynamicRowCell> rowCellMap, SpreadSheetCell cell,
                                  String cellName, String value) {
        try {
            Double.parseDouble(value);
            rowCellMap.put(totalCellNameAndIdMap.get(cellName), new DynamicRowCell(value));
        } catch (NumberFormatException e) {
            addRowLog(result, cell.getRowNum(), cell.getColNum(), String.format(NUMBER_INVALID, cellName));
        }
    }

    private boolean needCheck(String cellName, Object cellValue) {
        return cellValue != null
                && !ExcelDefaultConfig.COMMON_SPECIFICATION_ROWNUM.equals(cellName)
                && !SIHFConstants.STAFF_NAME_CELL_NAME.equals(cellName)
                && !SIHFConstants.MOBLIE_NO_CELL_NAME.equals(cellName);
    }

    /**
     * 获取指定公司的有效的、已被关联使用的社保公积金方案数据
     *
     * @param allValidCompanyBenefits
     * @param existedStaffLedgers
     * @param newStaffLedgers
     * @return
     */

    private List<CompanyBenefitVo> getAllUsedValidCompanyBenefits(List<CompanyBenefitVo> allValidCompanyBenefits,
                                                                  List<MonthlyLedger> existedStaffLedgers,
                                                                  List<MonthlyLedger> newStaffLedgers) {
        if (CollectionUtils.isEmpty(allValidCompanyBenefits)) {
            return Collections.emptyList();
        }

        Map<Long, CompanyBenefitVo> validIdBenefitMap = allValidCompanyBenefits.stream().collect(
                Collectors.toMap(CompanyBenefitVo::getId, self -> self));

        Map<Long, CompanyBenefitVo> resultMap = Maps.newHashMap();
        Map<String, MonthlyLedger> existedStaffIdLedgerMap = Maps.newHashMap();
        existedStaffLedgers.forEach(item -> existedStaffIdLedgerMap.put(item.getStaff().getId(), item));

        if (CollectionUtils.isNotEmpty(newStaffLedgers)) {
            newStaffLedgers.forEach(item -> {
                String staffId = item.getStaff().getId();
                if (existedStaffIdLedgerMap.containsKey(staffId)) {
                    existedStaffIdLedgerMap.remove(staffId);
                }
                CompanyBenefitSnapshot siCompanyBenefit = item.getSiCompanyBenefit();
                if (siCompanyBenefit != null && validIdBenefitMap.containsKey(siCompanyBenefit.getId())) {
                    resultMap.put(siCompanyBenefit.getId(), validIdBenefitMap.get(siCompanyBenefit.getId()));
                }
                CompanyBenefitSnapshot hfCompanyBenefit = item.getHfCompanyBenefit();
                if (hfCompanyBenefit != null && validIdBenefitMap.containsKey(hfCompanyBenefit.getId())) {
                    resultMap.put(hfCompanyBenefit.getId(), validIdBenefitMap.get(hfCompanyBenefit.getId()));
                }
            });
        }

        if (CollectionUtils.isNotEmpty(existedStaffIdLedgerMap)) {
            existedStaffIdLedgerMap.forEach((staffId, ledger) -> {
                CompanyBenefitSnapshot siCompanyBenefit = ledger.getSiCompanyBenefit();
                if (siCompanyBenefit != null && validIdBenefitMap.containsKey(siCompanyBenefit.getId())) {
                    resultMap.put(siCompanyBenefit.getId(), validIdBenefitMap.get(siCompanyBenefit.getId()));
                }
                CompanyBenefitSnapshot hfCompanyBenefit = ledger.getHfCompanyBenefit();
                if (hfCompanyBenefit != null && validIdBenefitMap.containsKey(hfCompanyBenefit.getId())) {
                    resultMap.put(hfCompanyBenefit.getId(), validIdBenefitMap.get(hfCompanyBenefit.getId()));
                }
            });
        }
        return Lists.newArrayList(resultMap.values());
    }

    private BenefitNameInfo getGroupedBenefitName(List<CompanyBenefitVo> companyBenefits) {
        BenefitNameInfo benefitNameInfo = new BenefitNameInfo();
        if (CollectionUtils.isNotEmpty(companyBenefits)) {
            companyBenefits.forEach(item -> {
                BenefitCategory category = item.getGroup().getCategory();
                if (category == BenefitCategory.SI) {
                    benefitNameInfo.addSiBenefitName(item.getName());
                } else if (category == BenefitCategory.HF) {
                    benefitNameInfo.addHfBenefitName(item.getName());
                }
            });
        }
        return benefitNameInfo;
    }

    private Map<String, String> getExcelHeaderMap(MonthlyLedgerSummary ledgerSummary) {
        return getExcelWholeHeader(ledgerSummary.getHeader() == null ? Collections.emptyList() : ledgerSummary.getHeader().getHeader());
    }

    private Map<String, ExcelCellStyle> getHeaderStyleMap() {
        ExcelCellStyle requiredStyle = ExcelCellStyleFactory.createRequiredHeaderCellStyle();
        Map<String, ExcelCellStyle> headerStyleMap = Maps.newHashMap();
        headerStyleMap.put(SIHFConstants.STAFF_NAME_CELL_ID, requiredStyle);
        return headerStyleMap;
    }

    private Map<String, CellComment> getHeaderCommentMap(BenefitNameInfo benefitNameInfo) {
        Map<String, CellComment> headerCommentMap = Maps.newHashMap();
        CellComment mobileNoCellComment = CellComment.createDefaultCellComment("姓名相同的员工，手机号必须填写，且不能相同");
        headerCommentMap.put(SIHFConstants.MOBLIE_NO_CELL_ID, mobileNoCellComment);
        CellComment cellComment = CellComment.createDefaultCellComment("社保方案名称与公积金方案名称至少一项不为空");
        if (CollectionUtils.isNotEmpty(benefitNameInfo.getSiBenefitNames())) {
            headerCommentMap.put(SIHFConstants.SI_BENEFIT_NAME_CELL_ID, cellComment);
        }
        if (CollectionUtils.isNotEmpty(benefitNameInfo.getHfBenefitNames())) {
            headerCommentMap.put(SIHFConstants.HF_BENEFIT_NAME_CELL_ID, cellComment);
        }

        return headerCommentMap;
    }

    private Map<String, List<String>> getDropDownsMap(BenefitNameInfo benefitNameInfo) {
        Map<String, List<String>> dropDownsMap = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(benefitNameInfo.getSiBenefitNames())) {
            dropDownsMap.put(SIHFConstants.SI_BENEFIT_NAME_CELL_ID, benefitNameInfo.getSiBenefitNames());
        }
        if (CollectionUtils.isNotEmpty(benefitNameInfo.getHfBenefitNames())) {
            dropDownsMap.put(SIHFConstants.HF_BENEFIT_NAME_CELL_ID, benefitNameInfo.getHfBenefitNames());
        }
        return dropDownsMap;
    }

    /**
     * 获取完整的表头(包含固定列与动态列)
     *
     * @param dynamicHeaderCells
     * @return
     */
    private Map<String, String> getExcelWholeHeader(List<DynamicHeaderCell> dynamicHeaderCells) {
        Map<String, String> headerMap = Maps.newLinkedHashMap();
        headerMap.put(SIHFConstants.STAFF_NAME_CELL_ID, SIHFConstants.STAFF_NAME_CELL_NAME);
        headerMap.put(SIHFConstants.MOBLIE_NO_CELL_ID, SIHFConstants.MOBLIE_NO_CELL_NAME);
        headerMap.put(SIHFConstants.ID_CARD_NO_CELL_ID, SIHFConstants.ID_CARD_NO_CELL_NAME);
        headerMap.put(SIHFConstants.DEPARTMENT_NAME_CELL_ID, SIHFConstants.DEPARTMENT_NAME_CELL_NAME);
        headerMap.put(SIHFConstants.DATA_SOURCE_CELL_ID, SIHFConstants.DATA_SOURCE_CELL_NAME);
        headerMap.put(SIHFConstants.SI_BENEFIT_NAME_CELL_ID, SIHFConstants.SI_BENEFIT_NAME_CELL_NAME);
        headerMap.put(SIHFConstants.SI_BASE_CELL_ID, SIHFConstants.SI_BASE_CELL_NAME);
        headerMap.put(SIHFConstants.HF_BENEFIT_NAME_CELL_ID, SIHFConstants.HF_BENEFIT_NAME_CELL_NAME);
        headerMap.put(SIHFConstants.HF_BASE_CELL_ID, SIHFConstants.HF_BASE_CELL_NAME);
        if (CollectionUtils.isNotEmpty(dynamicHeaderCells)) {
            dynamicHeaderCells.forEach(item -> headerMap.put(item.getCellId(), item.getCellName()));
        }
        return headerMap;
    }

    /**
     * 下载模板时,获取表头
     *
     * @param dynamicHeaderCells
     * @param benefitNameInfo
     * @return
     */
    private Map<String, String> getTemplateWholeHeader(List<DynamicHeaderCell> dynamicHeaderCells, BenefitNameInfo benefitNameInfo) {
        Map<String, String> headerMap = Maps.newLinkedHashMap();
        headerMap.put(SIHFConstants.STAFF_NAME_CELL_ID, SIHFConstants.STAFF_NAME_CELL_NAME);
        headerMap.put(SIHFConstants.MOBLIE_NO_CELL_ID, SIHFConstants.MOBLIE_NO_CELL_NAME);
        if (CollectionUtils.isNotEmpty(benefitNameInfo.getSiBenefitNames())) {
            headerMap.put(SIHFConstants.SI_BENEFIT_NAME_CELL_ID, SIHFConstants.SI_BENEFIT_NAME_CELL_NAME);
        }
        if (CollectionUtils.isNotEmpty(benefitNameInfo.getHfBenefitNames())) {
            headerMap.put(SIHFConstants.HF_BENEFIT_NAME_CELL_ID, SIHFConstants.HF_BENEFIT_NAME_CELL_NAME);
        }
        if (CollectionUtils.isNotEmpty(dynamicHeaderCells)) {
            dynamicHeaderCells.forEach(item -> headerMap.put(item.getCellId(), item.getCellName()));
        }
        return headerMap;
    }

    private List<List<Object>> getExcelData(Map<String, String> headers, Map<Long, String> departmentFullNames, DynamicFooter footer, String companyId, String userId,
                                            MonthlyLedgerRequest req) {
        Set<String> keySet = headers.keySet();
        List<String> dynamicHeaderCells = Lists.newArrayList(keySet);
        dynamicHeaderCells.remove(SIHFConstants.STAFF_NAME_CELL_ID);
        dynamicHeaderCells.remove(SIHFConstants.MOBLIE_NO_CELL_ID);
        dynamicHeaderCells.remove(SIHFConstants.ID_CARD_NO_CELL_ID);
        dynamicHeaderCells.remove(SIHFConstants.DEPARTMENT_NAME_CELL_ID);
        dynamicHeaderCells.remove(SIHFConstants.DATA_SOURCE_CELL_ID);
        dynamicHeaderCells.remove(SIHFConstants.SI_BENEFIT_NAME_CELL_ID);
        dynamicHeaderCells.remove(SIHFConstants.SI_BASE_CELL_ID);
        dynamicHeaderCells.remove(SIHFConstants.HF_BENEFIT_NAME_CELL_ID);
        dynamicHeaderCells.remove(SIHFConstants.HF_BASE_CELL_ID);

        if (CollectionUtils.isEmpty(dynamicHeaderCells)) {
            return Collections.emptyList();
        }

        Specification<MonthlyLedger> whereClause = getLedgerListWhereClause(companyId, userId, req);
        List<MonthlyLedger> monthlyLedgers = reposity.findAll(whereClause);
        if (CollectionUtils.isEmpty(monthlyLedgers)) {
            return Collections.emptyList();
        }
        List<List<Object>> result = Lists.newArrayListWithCapacity(monthlyLedgers.size());
        monthlyLedgers.forEach(item -> {
            List<Object> row = Lists.newArrayList();
            row.add(item.getStaff().getStaffName());
            row.add(item.getStaff().getMobileNo());
            row.add(item.getStaff().getIdCardNo());
            row.add(departmentFullNames.get(item.getStaff().getDepartmentId()));
            row.add(item.getDataSource().getTitle());
            row.add(item.getSiCompanyBenefit() == null ? EXCEL_EMPTY_VALUE : item.getSiCompanyBenefit().getName());
            BenefitSnapshot socialInsurance = item.getStaffSocialInsurance();
            row.add(socialInsurance == null || socialInsurance.getBase() == null ? EXCEL_EMPTY_VALUE : socialInsurance.getBase() / 100);
            row.add(item.getHfCompanyBenefit() == null ? EXCEL_EMPTY_VALUE : item.getHfCompanyBenefit().getName());
            BenefitSnapshot houseFund = item.getStaffHouseFund();
            row.add(houseFund == null || houseFund.getBase() == null ? EXCEL_EMPTY_VALUE : houseFund.getBase() / 100);

            DynamicRow dynamicRow = item.getCells();
            Map<String, DynamicRowCell> cells = dynamicRow.getCells();
            if (CollectionUtils.isEmpty(cells)) {
                return;
            }
            dynamicHeaderCells.forEach(dynamicHeaderCell -> {
                DynamicRowCell dynamicRowCell = cells.get(dynamicHeaderCell);
                row.add(dynamicRowCell == null || dynamicRowCell.getCellValue() == null
                        ? EXCEL_EMPTY_VALUE : dynamicRowCell.getCellValue());
            });
            result.add(row);
        });
        if (footer != null && footer.getFooter() != null) {
            Map<String, DynamicRowCell> cells = footer.getFooter().getCells();
            if (CollectionUtils.isNotEmpty(cells)) {
                List<Object> row = Lists.newArrayList();
                row.add("合计");
                row.add(EXCEL_EMPTY_VALUE);
                row.add(EXCEL_EMPTY_VALUE);
                row.add(EXCEL_EMPTY_VALUE);
                row.add(EXCEL_EMPTY_VALUE);
                row.add(EXCEL_EMPTY_VALUE);
                row.add(EXCEL_EMPTY_VALUE);
                row.add(EXCEL_EMPTY_VALUE);
                row.add(EXCEL_EMPTY_VALUE);
                dynamicHeaderCells.forEach(dynamicHeaderCell -> {
                    DynamicRowCell dynamicRowCell = cells.get(dynamicHeaderCell);
                    row.add(dynamicRowCell == null || dynamicRowCell.getCellValue() == null
                            ? DEFAULT_CELL_VALUE_NUMBER : dynamicRowCell.getCellValue());
                });
                result.add(row);
            }
        }
        return result;
    }

    private Specification<MonthlyLedger> getLedgerListWhereClause(String companyId, String userId, MonthlyLedgerRequest req) {
        return (root, q, cb) -> {

            Predicate predicate = cb.conjunction();
            predicate.getExpressions().add(cb.equal(getPath(root, MonthlyLedger.Field.companyId).as(String.class), companyId));
            predicate.getExpressions().add(cb.equal(getPath(root, MonthlyLedger.Field.userId).as(String.class), userId));
            predicate.getExpressions().add(cb.equal(getPath(root, MonthlyLedger.Field.ledgerYear).as(Integer.class), req.getLedgerYear()));
            predicate.getExpressions().add(cb.equal(getPath(root, MonthlyLedger.Field.ledgerMonth).as(Integer.class), req.getLedgerMonth()));

            if (CollectionUtils.isNotEmpty(req.getSiCompanyBenefitIds())) {
                predicate.getExpressions().add(getPath(root, MonthlyLedger.Field.siCompanyBenefit_id).as(Long.class)
                        .in(req.getSiCompanyBenefitIds()));
            }

            if (CollectionUtils.isNotEmpty(req.getHfCompanyBenefitIds())) {
                predicate.getExpressions().add(getPath(root, MonthlyLedger.Field.hfCompanyBenefit_id).as(Long.class)
                        .in(req.getHfCompanyBenefitIds()));
            }

            Join<MonthlyLedger, SyncedBasicStaffInfo> staff = root.join(StaffBenefit.Field.staff, JoinType.LEFT);
            if (StringUtils.isNotBlank(req.getStaffName())) {
                predicate.getExpressions().add(
                        cb.like(staff.get(SyncedBasicStaffInfo.Field.staffName).as(String.class), req.getStaffName() + "%"));
            }

            if (StringUtils.isNotBlank(req.getIdCardNo())) {
                predicate.getExpressions().add(
                        cb.like(staff.get(SyncedBasicStaffInfo.Field.idCardNo).as(String.class), req.getIdCardNo() + "%"));
            }

            if (CollectionUtils.isNotEmpty(req.getDepartmentIds())) {
                predicate.getExpressions().add(
                        staff.get(SyncedBasicStaffInfo.Field.departmentId).as(Long.class).in(req.getDepartmentIds()));
            }

            return predicate;
        };
    }

    private Path<MonthlyLedger> getPath(Path<MonthlyLedger> root, String attributeName) {
        String[] attributes = attributeName.split("\\.");
        Path<MonthlyLedger> path = root;
        for (String attribute : attributes) {
            path = path.get(attribute);
        }
        return path;
    }

    private Pageable getPageable(Integer pageNo, Integer pageSize, String sortString) {
        int page = pageNo == null ? 0 : pageNo;
        if (page > 0) {
            page--;
        }
        Sort sort = null;
        if (StringUtils.isNotBlank(sortString)) {
            String[] o = sortString.split(",");
            Sort.Order order;
            if (o.length == 2) {
                order = new Sort.Order(Sort.Direction.fromString(o[1]), o[0]);
                sort = new Sort(order);
            } else if (o.length == 1) {
                order = new Sort.Order(Sort.Direction.ASC, o[0]);
                sort = new Sort(order);
            }
        }
        return new PageRequest(page, pageSize == null ? 0 : pageSize, sort);
    }

    private void validateLedgerDate(Integer ledgerYear, Integer ledgerMonth) {
        ValidationUtils.rejectIfEmpty(ledgerYear, "", "台账年份不能为空");
        ValidationUtils.rejectIfEmpty(ledgerMonth, "", "台账月份不能为空");
        ValidationUtils.rejectNotInRange(ledgerMonth, 1, 12, "", "台账月份无效");
    }

    private Date getLedgerDate(int ledgerYear, int ledgerMonth) {
        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.YEAR, ledgerYear);
        calendar.set(Calendar.MONTH, ledgerMonth - 1);
        calendar.set(Calendar.DATE, 1);
        return DateUtils.dateFormat(calendar.getTime(), DateUtils.STANDARD_DATE_FORMAT_YMD);
    }

    private void validateCompanyBenefits(BenefitNameInfo benefitNameInfo) {
        if (CollectionUtils.isEmpty(benefitNameInfo.getSiBenefitNames())
                && CollectionUtils.isEmpty(benefitNameInfo.getHfBenefitNames())) {
            ValidationUtils.reject("", "当前月度下的社保和公积金方案均无效，无法生成模板！");
        }
    }

    /**
     * 校验添加的员工信息；
     * 将有效的员工转化为台账Dto数据
     *
     * @param userId
     * @param allValidCompanyBenefits
     * @param staffBenefits
     * @param ledgerYear
     * @param ledgerMonth
     * @param ledgerResponse
     * @return
     */
    private List<MonthlyLedger> validateAddedStaffLedgers(String userId,
                                                          List<CompanyBenefitVo> allValidCompanyBenefits,
                                                          List<StaffBenefitVo> staffBenefits,
                                                          int ledgerYear, int ledgerMonth,
                                                          LedgerResponse ledgerResponse) {

        Date ledgerDate = getLedgerDate(ledgerYear, ledgerMonth);
        //方案id
        List<Long> validIds = allValidCompanyBenefits.stream().map(CompanyBenefitVo::getId).collect(Collectors.toList());

        List<MonthlyLedger> newStaffLedgerList = Lists.newArrayList();
        staffBenefits.forEach(item -> {
            String staffName = item.getStaff().getStaffName();

            BenefitVo socialInsurance = item.getSocialInsurance();
            boolean siBenefitValid = isSIHFBenefitValid(validIds, socialInsurance);
            boolean staffSIDateValid = isStaffSIHFBenefitDateValid(socialInsurance, ledgerDate);
            if (!siBenefitValid) {
                ledgerResponse.addErrorInfo(String.format(SI_INVALID, staffName));
            }
            if (!staffSIDateValid) {
                ledgerResponse.addErrorInfo(String.format(SI_DATE_INVALID, staffName));
            }

            BenefitVo houseFund = item.getHouseFund();
            boolean hfBenefitValid = isSIHFBenefitValid(validIds, houseFund);
            boolean staffHFDateValid = isStaffSIHFBenefitDateValid(houseFund, ledgerDate);

            if (!hfBenefitValid) {
                ledgerResponse.addErrorInfo(String.format(HF_INVALID, staffName));
            }
            if (!staffHFDateValid) {
                ledgerResponse.addErrorInfo(String.format(HF_DATE_INVALID, staffName));
            }

            boolean siValid = siBenefitValid && staffSIDateValid;
            boolean hfValid = hfBenefitValid && staffHFDateValid;
            if (!siValid && !hfValid) {
                return;
            }
            MonthlyLedger monthlyLedger = new MonthlyLedger();
            initStaffLedgerBaseInfo(userId, ledgerYear, ledgerMonth, item, monthlyLedger);

            if (siValid) {
                CompanyBenefitSnapshot siCompanyBenefit = new CompanyBenefitSnapshot();
                siCompanyBenefit.setId(socialInsurance.getCompanyBenefitId());
                siCompanyBenefit.setName(socialInsurance.getCompanyBenefitName());
                monthlyLedger.setSiCompanyBenefit(siCompanyBenefit);
                if (Boolean.TRUE.equals(socialInsurance.getCalculatable())) {
                    BenefitSnapshot siStaffBenefit = new BenefitSnapshot();
                    siStaffBenefit.setStartOn(socialInsurance.getStartOn());
                    siStaffBenefit.setEndOn(socialInsurance.getEndOn());
                    siStaffBenefit.setBase(socialInsurance.getBase());
                    siStaffBenefit.setCalculatable(socialInsurance.getCalculatable());
                    monthlyLedger.setStaffSocialInsurance(siStaffBenefit);
                } else {
                    ledgerResponse.addErrorInfo(String.format(SI_NOT_CALCULATE_INFO, staffName));
                }
            }
            if (hfValid) {
                CompanyBenefitSnapshot hfCompanyBenefit = new CompanyBenefitSnapshot();
                hfCompanyBenefit.setId(houseFund.getCompanyBenefitId());
                hfCompanyBenefit.setName(houseFund.getCompanyBenefitName());
                monthlyLedger.setHfCompanyBenefit(hfCompanyBenefit);
                if (Boolean.TRUE.equals(houseFund.getCalculatable())) {
                    BenefitSnapshot hfStaffBenefit = new BenefitSnapshot();
                    hfStaffBenefit.setStartOn(houseFund.getStartOn());
                    hfStaffBenefit.setEndOn(houseFund.getEndOn());
                    hfStaffBenefit.setBase(houseFund.getBase());
                    hfStaffBenefit.setCalculatable(houseFund.getCalculatable());
                    monthlyLedger.setStaffHouseFund(hfStaffBenefit);
                } else {
                    ledgerResponse.addErrorInfo(String.format(HF_NOT_CALCULATE_INFO, staffName));
                }
            }
            newStaffLedgerList.add(monthlyLedger);
        });
        return newStaffLedgerList;
    }

    private void initStaffLedgerBaseInfo(String userId, int ledgerYear, int ledgerMonth, StaffBenefitVo item, MonthlyLedger monthlyLedger) {
        monthlyLedger.setLedgerYear(ledgerYear);
        monthlyLedger.setLedgerMonth(ledgerMonth);
        monthlyLedger.setDataSource(LedgerItemDataSource.CALC_ONLINE);
        monthlyLedger.setUserId(userId);
        SyncedBasicStaffInfo staffInfo = new SyncedBasicStaffInfo();
        staffInfo.setId(item.getStaff().getId());
        staffInfo.setStaffName(item.getStaff().getStaffName());
        monthlyLedger.setStaff(staffInfo);
    }

    private boolean isSIHFValid(Collection<Long> validBenefitIds, BenefitVo benefit, Date ledgerDate) {
        if (benefit == null) {
            return false;
        }
        Long companyBenefitId = benefit.getCompanyBenefitId();
        return companyBenefitId != null && validBenefitIds.contains(companyBenefitId)
                && isStaffSIHFBenefitDateValid(benefit, ledgerDate);
    }

    private boolean isSIHFBenefitValid(Collection<Long> validBenefitIds, BenefitVo benefit) {
        if (benefit == null) {
            return false;
        }
        Long companyBenefitId = benefit.getCompanyBenefitId();
        return companyBenefitId != null && validBenefitIds.contains(companyBenefitId);
    }

    private boolean isStaffSIHFBenefitDateValid(BenefitVo benefit, Date ledgerDate) {
        if (benefit == null) {
            return false;
        }
        Date startOn = benefit.getStartOn();
        Date endOn = benefit.getEndOn();
        return (startOn != null && ledgerDate.compareTo(startOn) >= 0)
                && (endOn == null || ledgerDate.compareTo(endOn) <= 0);
    }

    /**
     * 从方案中获取员工台账总的表头数据
     *
     * @param validCompanyBenefits
     * @return
     */
    private List<DynamicHeaderCell> getSIHFHeader(List<CompanyBenefitVo> validCompanyBenefits) {
        if (CollectionUtils.isEmpty(validCompanyBenefits)) {
            return Collections.emptyList();
        }
        Map<Long, BenefitTypeVo> enabledTypeMap = Maps.newHashMap();
        validCompanyBenefits.forEach(item -> {
            BenefitGroupVo group = item.getGroup();
            List<BenefitTemplateVo> templates = group.getTemplates();
            templates.forEach(template -> {
                BenefitTypeVo type = template.getType();
                if (!enabledTypeMap.containsKey(type.getId())) {
                    enabledTypeMap.put(type.getId(), type);
                }
            });
        });
        return getHeaderCells(Lists.newArrayList(enabledTypeMap.values()));
    }

    /**
     * 获取员工台账的单元格数据（pay数据）
     *
     * @param staffLedgerList      员工台账
     * @param validCompanyBenefits 有效方案
     * @param ledgerYear
     * @param ledgerMonth
     * @return 结果格式：<staffId,<cellId,cellValue>>
     */
    private Map<String, Map<String, DynamicRowCell>> getStaffCellValue(List<MonthlyLedger> staffLedgerList,
                                                                       List<CompanyBenefitVo> validCompanyBenefits,
                                                                       int ledgerYear, int ledgerMonth,
                                                                       LedgerResponse ledgerResponse) {
        if (CollectionUtils.isEmpty(staffLedgerList)) {
            return Maps.newHashMap();
        }
        Date ledgerDate = getLedgerDate(ledgerYear, ledgerMonth);

        Map<Long, CompanyBenefitVo> benefitVoMap = validCompanyBenefits.stream()
                .collect(Collectors.toMap(CompanyBenefitVo::getId, s -> s));

        Map<Long, Map<String, Pair<BenefitTypeVo, IBenefitPaymentConfig>>> benefitCellPaymentMap
                = getBenefitCellPaymentConfigMap(validCompanyBenefits);
        //结果
        Map<String, Map<String, DynamicRowCell>> result = Maps.newHashMap();
        staffLedgerList.forEach(item -> {
            String staffId = item.getStaff().getId();
            Map<String, DynamicRowCell> cellValueMap = Maps.newHashMap();
            //社保公积金个人/公司合计
            TotalCellValue totalCellValue = new TotalCellValue();
            //社保方案
            CompanyBenefitSnapshot siCompanyBenefit = item.getSiCompanyBenefit();
            BenefitSnapshot socialinsurance = item.getStaffSocialInsurance();
            String staffName = item.getStaff().getStaffName();
            if (siCompanyBenefit != null && benefitCellPaymentMap.containsKey(siCompanyBenefit.getId())
                    && isLedgerSIHFDateValid(socialinsurance, ledgerDate)) {
                if (socialinsurance.getBase() == null) {
                    ledgerResponse.addErrorInfo(String.format(SI_BASE_NOT_SETTING, staffName));
                } else {
                    Map<String, Pair<BenefitTypeVo, IBenefitPaymentConfig>> cellPayment = benefitCellPaymentMap.get(siCompanyBenefit.getId());
                    calculateCellValueSi(cellValueMap, totalCellValue, socialinsurance, cellPayment);
                }
                cellValueMap.put(SIHFConstants.SI_ADJUST_HEADER_CELL_ID_PERSON, new DynamicRowCell(DEFAULT_CELL_VALUE_NUMBER));
                cellValueMap.put(SIHFConstants.SI_ADJUST_HEADER_CELL_ID_COMPANY, new DynamicRowCell(DEFAULT_CELL_VALUE_NUMBER));
            }
            //公积金方案
            CompanyBenefitSnapshot hfCompanyBenefit = item.getHfCompanyBenefit();
            BenefitSnapshot houseFund = item.getStaffHouseFund();
            if (hfCompanyBenefit != null && benefitCellPaymentMap.containsKey(hfCompanyBenefit.getId())
                    && isLedgerSIHFDateValid(houseFund, ledgerDate)) {
                if (houseFund.getBase() == null) {
                    ledgerResponse.addErrorInfo(String.format(HF_BASE_NOT_SETTING, staffName));
                } else {
                    Map<String, Pair<BenefitTypeVo, IBenefitPaymentConfig>> cellPayment = benefitCellPaymentMap.get(hfCompanyBenefit.getId());
                    CompanyBenefitVo companyBenefitVo = benefitVoMap.get(hfCompanyBenefit.getId());
                    calculateCellValueHf(cellValueMap, totalCellValue, houseFund, companyBenefitVo, cellPayment);

                }
                cellValueMap.put(SIHFConstants.HF_ADJUST_HEADER_CELL_ID_PERSON, new DynamicRowCell(DEFAULT_CELL_VALUE_NUMBER));
                cellValueMap.put(SIHFConstants.HF_ADJUST_HEADER_CELL_ID_COMPANY, new DynamicRowCell(DEFAULT_CELL_VALUE_NUMBER));
            }

            cellValueMap.put(SIHFConstants.SI_TOTAL_HEADER_CELL_ID_PERSON, new DynamicRowCell(totalCellValue.getSiPersonTotal().toString()));
            cellValueMap.put(SIHFConstants.SI_TOTAL_HEADER_CELL_ID_COMPANY, new DynamicRowCell(totalCellValue.getSiCompanyTotal().toString()));
            cellValueMap.put(SIHFConstants.HF_TOTAL_HEADER_CELL_ID_PERSON, new DynamicRowCell(totalCellValue.getHfPersonTotal().toString()));
            cellValueMap.put(SIHFConstants.HF_TOTAL_HEADER_CELL_ID_COMPANY, new DynamicRowCell(totalCellValue.getHfCompanyTotal().toString()));
            cellValueMap.put(SIHFConstants.SI_HF_TOTAL_HEADER_CELL_ID_PERSON, new DynamicRowCell(totalCellValue.getSiHfPersonTotal().toString()));
            cellValueMap.put(SIHFConstants.SI_HF_TOTAL_HEADER_CELL_ID_COMPANY, new DynamicRowCell(totalCellValue.getSiHfCompanyTotal().toString()));
            cellValueMap.put(SIHFConstants.SI_TOTAL_HEADER_CELL_ID, new DynamicRowCell(totalCellValue.getSiTotal().toString()));
            cellValueMap.put(SIHFConstants.HF_TOTAL_HEADER_CELL_ID, new DynamicRowCell(totalCellValue.getHfTotal().toString()));
            cellValueMap.put(SIHFConstants.SI_HF_TOTAL_HEADER_CELL_ID, new DynamicRowCell(totalCellValue.getSIHFTotal().toString()));

            result.put(staffId, cellValueMap);
        });
        return result;
    }

    private boolean isLedgerSIHFDateValid(BenefitSnapshot benefit, Date ledgerDate) {
        if (benefit == null) {
            return false;
        }
        Date startOn = benefit.getStartOn();
        Date endOn = benefit.getEndOn();
        return (startOn != null && ledgerDate.compareTo(startOn) >= 0)
                && (endOn == null || ledgerDate.compareTo(endOn) <= 0);
    }

    /**
     * 获取指定公司的有效的社保公积金方案数据
     *
     * @param companyId
     * @param ledgerYear
     * @param ledgerMonth
     * @return
     */

    private List<CompanyBenefitVo> getAllValidCompanyBenefits(String companyId, int ledgerYear, int ledgerMonth) {
        List<CompanyBenefitVo> benefits = companyBenefitService.getEnabledCompanyBenefits(companyId);
        correctCompanyBenefits(benefits, ledgerYear, ledgerMonth);
        ValidationUtils.rejectIfEmpty(benefits, "", "当前月无可用的社保与公积金方案，请先进行方案设置");
        return benefits;
    }

    private void calculateCellValueSi(Map<String, DynamicRowCell> cellValueMap, TotalCellValue totalCellValue,
                                      BenefitSnapshot benefitSnapshot,
                                      Map<String, Pair<BenefitTypeVo, IBenefitPaymentConfig>> cellPayment) {
        for (Map.Entry<String, Pair<BenefitTypeVo, IBenefitPaymentConfig>> entry : cellPayment.entrySet()) {
            String cellId = entry.getKey();
            if (benefitSnapshot.getCalculatable() == null || !benefitSnapshot.getCalculatable()) {
                cellValueMap.put(cellId, new DynamicRowCell(null));
            }
            Pair<BenefitTypeVo, IBenefitPaymentConfig> paymentPair = entry.getValue();
            IBenefitPaymentConfig payment = paymentPair.getSecond();
            int base = benefitSnapshot.getBase();
            base = Math.min(base, payment.getBaseMax());
            base = Math.max(base, payment.getBaseMin());

            //计算规则：base/100d * payment.getRatio()/10000d + payment.getAmount()/100d
            long cellValueLong = (long) base * payment.getRatio() + 10000L * payment.getAmount();
            BigDecimal cellValue = new BigDecimal(cellValueLong)
                    .divide(DEVIDE_NUBMER, payment.getRoundBit(), payment.getRoundType().toRoundingMode());
            cellValueMap.put(cellId, new DynamicRowCell(cellValue.toString()));

            if (SIHFUtil.getHeaderCellType(cellId) == BenefitsPayer.PERSON) {
                totalCellValue.siPersonAdd(cellValue);
            } else {
                totalCellValue.siCompanyAdd(cellValue);
            }
        }
    }

    private void calculateCellValueHf(Map<String, DynamicRowCell> cellValueMap, TotalCellValue totalCellValue,
                                      BenefitSnapshot benefitSnapshot, CompanyBenefitVo companyBenefitVo,
                                      Map<String, Pair<BenefitTypeVo, IBenefitPaymentConfig>> cellPayment) {
        /*//公积金个人缴纳比例合计（基本公积金与补充公积金的个人缴纳部分比例合计）
        int hfPersonPayRatioTotal = 0;*/

        for (Map.Entry<String, Pair<BenefitTypeVo, IBenefitPaymentConfig>> entry : cellPayment.entrySet()) {
            String cellId = entry.getKey();
            if (benefitSnapshot.getCalculatable() == null || !benefitSnapshot.getCalculatable()) {
                cellValueMap.put(cellId, new DynamicRowCell(null));
            }
            Pair<BenefitTypeVo, IBenefitPaymentConfig> paymentPair = entry.getValue();
            IBenefitPaymentConfig payment = paymentPair.getSecond();
            int base = benefitSnapshot.getBase();
            base = Math.min(base, payment.getBaseMax());
            base = Math.max(base, payment.getBaseMin());

            //计算规则：base/100d * payment.getRatio()/10000d + payment.getAmount()/100d
            int ratio = payment.getRatio();
            long cellValueLong = (long) base * ratio + 10000L * payment.getAmount();
            BigDecimal cellValue = new BigDecimal(cellValueLong)
                    .divide(DEVIDE_NUBMER, payment.getRoundBit(), payment.getRoundType().toRoundingMode());
            cellValueMap.put(cellId, new DynamicRowCell(cellValue.toString()));

//            BenefitTypeVo first = paymentPair.getFirst();

            if (SIHFUtil.getHeaderCellType(cellId) == BenefitsPayer.PERSON) {
                /*if (first.getHfSubType() == HFSubType.FIXED) {
                    hfPersonPayRatioTotal = hfPersonPayRatioTotal + ratio;
                }*/

                totalCellValue.hfPersonAdd(cellValue);
            } else {
                totalCellValue.hfCompanyAdd(cellValue);
            }
        }

        /*//公积金应税调整值
        Integer hfTaxFreeRatio = companyBenefitVo.getHfTaxFreeRatio();
        if (hfTaxFreeRatio != null
                && hfTaxFreeRatio < hfPersonPayRatioTotal) {
            long valueLong = (long) benefitSnapshot.getBase() * (hfPersonPayRatioTotal - hfTaxFreeRatio);
            BigDecimal value = new BigDecimal(valueLong).divide(DEVIDE_NUBMER, 2, BigDecimal.ROUND_HALF_UP);
            totalCellValue.hfPersonAdd(value);
            cellValueMap.put(SIHFConstants.HF_TAX_ADJUST_CELL_ID_PERSON, new DynamicRowCell(value.toString()));
        }*/

    }

    /**
     * 统一将方案中的数据转换成单元格相关数据
     *
     * @param benefits 本方法不做校验工作，仅处理数据
     * @return 结果格式：<CompanyBenefit-id,<cellId,<benefitTypeVo,paymentConfig>>>
     */
    private Map<Long, Map<String, Pair<BenefitTypeVo, IBenefitPaymentConfig>>> getBenefitCellPaymentConfigMap(List<CompanyBenefitVo> benefits) {
        Map<Long, Map<String, Pair<BenefitTypeVo, IBenefitPaymentConfig>>> result = Maps.newHashMap();
        benefits.forEach(item -> {
            Map<String, Pair<BenefitTypeVo, IBenefitPaymentConfig>> cellPaymentMap = Maps.newHashMap();
            List<BenefitTemplateVo> templates = item.getGroup().getTemplates();
            templates.forEach(template -> {
                BenefitTypeVo type = template.getType();
                String typeCode = type.getCode();

                //除工伤和生育保险以外的险种才需要生成个人项
                if (!SIHFConstants.MATERNITY_INSURANCE_CODE.equals(typeCode)
                        && !SIHFConstants.EMPLOYMENT_INJURY_INSURANCE_CODE.equals(typeCode)) {
                    cellPaymentMap.put(SIHFUtil.getHeaderCellId(typeCode, BenefitsPayer.PERSON),
                            Pair.of(type, template.getConfigs().get(0).getPersonConfig()));
                }

                cellPaymentMap.put(SIHFUtil.getHeaderCellId(typeCode, BenefitsPayer.COMPANY),
                        Pair.of(type, template.getConfigs().get(0).getCompanyConfig()));
            });
            result.put(item.getId(), cellPaymentMap);
        });
        return result;
    }

    /**
     * 校正方案数据：
     * 1、移除无效方案
     * 2、将有效方案中的pay数据加工成计算需要的信息
     *
     * @param benefits    待处理方案数据
     * @param ledgerYear
     * @param ledgerMonth
     */
    private void correctCompanyBenefits(List<CompanyBenefitVo> benefits, int ledgerYear, int ledgerMonth) {
        if (CollectionUtils.isEmpty(benefits)) {
            return;
        }
        //获取系统中的可用险种数据
        Date ledgerDate = getLedgerDate(ledgerYear, ledgerMonth);
        List<BenefitTypeVo> enabledTypes = benefitsTypeService.getCurrentEnabledTypes(ledgerDate);
        ValidationUtils.rejectIfEmpty(enabledTypes, "", "当前月无有效的社保与公积金类型，请先进行类型设置");
        List<Long> enabledTypeIds = enabledTypes.stream().map(BenefitTypeVo::getId).collect(Collectors.toList());
        //方案数据
        Iterator<CompanyBenefitVo> benefitIterator = benefits.iterator();
        while (benefitIterator.hasNext()) {
            CompanyBenefitVo benefit = benefitIterator.next();
            BenefitGroupVo group = benefit.getGroup();
            //方案无效，移除
            if (group == null || CollectionUtils.isEmpty(group.getTemplates())) {
                benefitIterator.remove();
                continue;
            }
            //方案险种
            List<BenefitTemplateVo> templates = group.getTemplates();
            //方案险种的自定义pay数据
            List<CompanyBenefitConfigVo> customConfigs = benefit.getBenefitConfigs();
            Iterator<BenefitTemplateVo> templateIterator = templates.iterator();
            while (templateIterator.hasNext()) {
                BenefitTemplateVo template = templateIterator.next();
                //险种的具体pay数据
                PaymentConfigVo validPaymentConfig = getValidTemplatePaymentConfig(template,
                        enabledTypeIds, customConfigs, ledgerDate);
                if (validPaymentConfig == null) {
                    templateIterator.remove();
                } else {
                    //保证每个有效方案的险种只存在一个pay数据
                    template.setConfigs(Lists.newArrayList(validPaymentConfig));
                }
            }
            if (CollectionUtils.isEmpty(templates)) {
                benefitIterator.remove();
            }
        }
    }

    /**
     * 获取模板（即险种）的有效数据
     *
     * @param template       具体险种
     * @param enabledTypeIds 系统中可用的险种id
     * @param customConfigs  方案中部分险种的自定义数据
     * @param currentDate    当前日期
     * @return 险种中的有效数据
     */
    private PaymentConfigVo getValidTemplatePaymentConfig(BenefitTemplateVo template, List<Long> enabledTypeIds,
                                                          List<CompanyBenefitConfigVo> customConfigs, Date currentDate) {
        BenefitTypeVo type = template.getType();
        if (type == null || !enabledTypeIds.contains(type.getId())) {
            return null;
        }
        PaymentConfigVo result;
        //优先获取系统数据
        result = getPaymentConfigInTemplate(template, currentDate);

        //存在自定义数据，则用自定义数据覆盖系统数据
        if (CollectionUtils.isEmpty(customConfigs)) {
            return result;
        }
        for (CompanyBenefitConfigVo customConfig : customConfigs) {
            //自定义数据
            if (customConfig.getType() != null && customConfig.getType().getId().equals(type.getId())) {
                //自定义数据无效
                if (customConfig.getEnabled() == null || !customConfig.getEnabled()
                        || !isBenefitConfigDateValid(currentDate, customConfig.getEffectiveOn(), customConfig.getInvalidOn())) {
                    result = null;
                } else {
                    result = new PaymentConfigVo();
                    result.setCompanyConfig(customConfig.getCompanyConfig());
                    result.setPersonConfig(customConfig.getPersonConfig());
                }
                break;
            }
        }
        return result;
    }

    private PaymentConfigVo getPaymentConfigInTemplate(BenefitTemplateVo template, Date currentDate) {
        List<PaymentConfigVo> paymentConfigs = template.getConfigs();
        if (CollectionUtils.isNotEmpty(paymentConfigs)) {
            for (PaymentConfigVo config : paymentConfigs) {
                if (isBenefitConfigDateValid(currentDate, config.getEffectiveOn(), config.getInvalidOn())) {
                    return config;
                }
            }
        }
        return null;
    }

    private boolean isBenefitConfigDateValid(Date currentDate, Date effectiveOn, Date invalidOn) {
        return effectiveOn != null && currentDate.compareTo(effectiveOn) >= 0 && (invalidOn == null || currentDate.compareTo(invalidOn) < 0);
    }

    private List<DynamicHeaderCell> getHeaderCells(List<BenefitTypeVo> enabledTypes) {
        if (CollectionUtils.isEmpty(enabledTypes)) {
            return Collections.emptyList();
        }
        List<DynamicHeaderCell> siPersonHeaderCells = Lists.newArrayList();
        List<DynamicHeaderCell> siCompanyHeaderCells = Lists.newArrayList();

        List<DynamicHeaderCell> hfPersonHeaderCells = Lists.newArrayList();
        List<DynamicHeaderCell> hfCompanyHeaderCells = Lists.newArrayList();

        /*//是否包含（基本公积金；补充公积金）
        boolean hasFixedHf = false;*/
        for (BenefitTypeVo item : enabledTypes) {
            BenefitCategory category = item.getCategory();
            if (BenefitCategory.SI == category) {
                //除工伤和生育保险以外的险种才需要生成个人项
                if (!SIHFConstants.MATERNITY_INSURANCE_CODE.equals(item.getCode())
                        && !SIHFConstants.EMPLOYMENT_INJURY_INSURANCE_CODE.equals(item.getCode())) {
                    DynamicHeaderCell headerCellPerson = getHeaderCell(item, BenefitsPayer.PERSON);
                    siPersonHeaderCells.add(headerCellPerson);
                }
                DynamicHeaderCell headerCellCompany = getHeaderCell(item, BenefitsPayer.COMPANY);
                siCompanyHeaderCells.add(headerCellCompany);
            } else if (BenefitCategory.HF == category) {
                /*if (item.getHfSubType() == HFSubType.FIXED) {
                    hasFixedHf = true;
                }*/
                DynamicHeaderCell headerCellPerson = getHeaderCell(item, BenefitsPayer.PERSON);
                hfPersonHeaderCells.add(headerCellPerson);
                DynamicHeaderCell headerCellCompany = getHeaderCell(item, BenefitsPayer.COMPANY);
                hfCompanyHeaderCells.add(headerCellCompany);
            }
        }

        List<DynamicHeaderCell> results = Lists.newArrayList();

        if (CollectionUtils.isNotEmpty(siPersonHeaderCells)) {
            results.addAll(siPersonHeaderCells);
            results.add(new DynamicHeaderCell(SIHFConstants.SI_ADJUST_HEADER_CELL_ID_PERSON,
                    SIHFConstants.SI_ADJUST_HEADER_CELL_NAME_PERSON));
            results.add(new DynamicHeaderCell(SIHFConstants.SI_TOTAL_HEADER_CELL_ID_PERSON,
                    SIHFConstants.SI_TOTAL_HEADER_CELL_NAME_PERSON));
        }

        if (CollectionUtils.isNotEmpty(hfPersonHeaderCells)) {
            results.addAll(hfPersonHeaderCells);
            results.add(new DynamicHeaderCell(SIHFConstants.HF_ADJUST_HEADER_CELL_ID_PERSON,
                    SIHFConstants.HF_ADJUST_HEADER_CELL_NAME_PERSON));
            /*if (hasFixedHf) {
                results.add(new DynamicHeaderCell(SIHFConstants.HF_TAX_ADJUST_CELL_ID_PERSON,
                        SIHFConstants.HF_TAX_ADJUST_CELL_NAME_PERSON));
            }*/
            results.add(new DynamicHeaderCell(SIHFConstants.HF_TOTAL_HEADER_CELL_ID_PERSON,
                    SIHFConstants.HF_TOTAL_HEADER_CELL_NAME_PERSON));
        }

        results.add(new DynamicHeaderCell(SIHFConstants.SI_HF_TOTAL_HEADER_CELL_ID_PERSON,
                SIHFConstants.SI_HF_TOTAL_HEADER_CELL_NAME_PERSON));

        if (CollectionUtils.isNotEmpty(siCompanyHeaderCells)) {
            results.addAll(siCompanyHeaderCells);
            results.add(new DynamicHeaderCell(SIHFConstants.SI_ADJUST_HEADER_CELL_ID_COMPANY,
                    SIHFConstants.SI_ADJUST_HEADER_CELL_NAME_COMPANY));
            results.add(new DynamicHeaderCell(SIHFConstants.SI_TOTAL_HEADER_CELL_ID_COMPANY,
                    SIHFConstants.SI_TOTAL_HEADER_CELL_NAME_COMPANY));
        }

        if (CollectionUtils.isNotEmpty(hfCompanyHeaderCells)) {
            results.addAll(hfCompanyHeaderCells);
            results.add(new DynamicHeaderCell(SIHFConstants.HF_ADJUST_HEADER_CELL_ID_COMPANY,
                    SIHFConstants.HF_ADJUST_HEADER_CELL_NAME_COMPANY));
            results.add(new DynamicHeaderCell(SIHFConstants.HF_TOTAL_HEADER_CELL_ID_COMPANY,
                    SIHFConstants.HF_TOTAL_HEADER_CELL_NAME_COMPANY));
        }

        results.add(new DynamicHeaderCell(SIHFConstants.SI_HF_TOTAL_HEADER_CELL_ID_COMPANY,
                SIHFConstants.SI_HF_TOTAL_HEADER_CELL_NAME_COMPANY));

        results.add(new DynamicHeaderCell(SIHFConstants.SI_TOTAL_HEADER_CELL_ID,
                SIHFConstants.SI_TOTAL_HEADER_CELL_NAME));
        results.add(new DynamicHeaderCell(SIHFConstants.HF_TOTAL_HEADER_CELL_ID,
                SIHFConstants.HF_TOTAL_HEADER_CELL_NAME));
        results.add(new DynamicHeaderCell(SIHFConstants.SI_HF_TOTAL_HEADER_CELL_ID,
                SIHFConstants.SI_HF_TOTAL_HEADER_CELL_NAME));
        return results;
    }

    private DynamicHeaderCell getHeaderCell(BenefitTypeVo item, BenefitsPayer payer) {
        DynamicHeaderCell headerCell = new DynamicHeaderCell();
        headerCell.setCellId(SIHFUtil.getHeaderCellId(item.getCode(), payer));
        headerCell.setCellName(SIHFUtil.getHeaderCellName(item.getName().replace(TARGET_STRING, REPLACE_STRING), payer));
        return headerCell;
    }

    private static class TotalCellValue {

        /**
         * 社保个人合计
         */
        private BigDecimal siPersonCellValue = new BigDecimal(0);
        /**
         * 公积金个人合计
         */
        private BigDecimal hfPersonCellValue = new BigDecimal(0);
        /**
         * 社保公司合计
         */
        private BigDecimal siCompanyCellValue = new BigDecimal(0);
        /**
         * 公积金公司合计
         */
        private BigDecimal hfCompanyCellValue = new BigDecimal(0);

        void siPersonAdd(BigDecimal add) {
            siPersonCellValue = siPersonCellValue.add(add);
        }

        void hfPersonAdd(BigDecimal add) {
            hfPersonCellValue = hfPersonCellValue.add(add);
        }

        void siCompanyAdd(BigDecimal add) {
            siCompanyCellValue = siCompanyCellValue.add(add);
        }

        void hfCompanyAdd(BigDecimal add) {
            hfCompanyCellValue = hfCompanyCellValue.add(add);
        }

        BigDecimal getSiPersonTotal() {
            return siPersonCellValue;
        }

        BigDecimal getHfPersonTotal() {
            return hfPersonCellValue;
        }

        BigDecimal getSiCompanyTotal() {
            return siCompanyCellValue;
        }

        BigDecimal getHfCompanyTotal() {
            return hfCompanyCellValue;
        }

        /**
         * 个人社保公积金总计
         */
        BigDecimal getSiHfPersonTotal() {
            return siPersonCellValue.add(hfPersonCellValue);
        }

        /**
         * 公积金总计
         */
        BigDecimal getSiHfCompanyTotal() {
            return siCompanyCellValue.add(hfCompanyCellValue);
        }

        /**
         * 社保总计
         */
        BigDecimal getSiTotal() {
            return siPersonCellValue.add(siCompanyCellValue);
        }

        /**
         * 公积金总计
         */
        BigDecimal getHfTotal() {
            return hfPersonCellValue.add(hfCompanyCellValue);
        }

        /**
         * 总计
         */
        BigDecimal getSIHFTotal() {
            return getSiTotal().add(getHfTotal());
        }
    }

    private static class BenefitNameInfo {
        private List<String> siBenefitNames = Lists.newArrayList();
        private List<String> hfBenefitNames = Lists.newArrayList();

        void addSiBenefitName(String siBenefitName) {
            siBenefitNames.add(siBenefitName);
        }

        void addHfBenefitName(String hfBenefitName) {
            hfBenefitNames.add(hfBenefitName);
        }

        List<String> getSiBenefitNames() {
            return siBenefitNames;
        }

        List<String> getHfBenefitNames() {
            return hfBenefitNames;
        }
    }

}